<?php
	/**
	* @package includes
	* @desc This include file contains the functions that our web app calls. (Main Functionallity)
	*/
	/**
	*@desc This function calls and collects unique park type information.
	*/
function getParkTypes()
{
	
	global $connection; // make accessible in function
	
	$query = "SELECT DISTINCT(type) FROM parks";
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		return FALSE;
	else	
		return $result;
		
}

	/**
	*@desc This function calls and collects unique equipment information.
	*/
function getDistinctEquip()
{

	global $connection; // make accessible in function
	
	$query = "SELECT equip_id, name FROM equipment";
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		return FALSE;
	else	
		return $result;


}

	/**
	*@desc This function calls and collects unique suburb information and orders in alphabetical order.
	*/
function getSuburbs()
{
	
	global $connection; // make accessible in function
	
	$query = "SELECT suburb_id, name, postcode FROM suburbs ORDER BY name";
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		return FALSE;
	else	
		return $result;
		
}

	/**
	*@desc This function calls and collects unique region information and orders in alphabetical order.
	*/
function getRegions()
{
	
	global $connection; // make accessible in function
	
	$query = "SELECT name FROM regions ORDER BY name";
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		return FALSE;
	else	
		return $result;
		
}

	/**
	*@desc This function adds the proposal information sent by user into the database.
	*@param int $user_id
	*@param string $comment
	*@param string $address
	*@param int $suburb_id
	*/
function addProposal($user_id, $comment, $address, $suburb_id)
{
	global $connection; 
	
	// Prepare query
	$query = "INSERT INTO proposals (proposal_id, user_id, comment, address, suburb_id) "
	. "VALUES (NULL, "
	. "'" . $user_id. "', "
	. "'" . $comment . "', "
	. "'" . $address . "', "
	. "'" . $suburb_id . "')";

	
	// Add Proposal
	if ($result = mysql_query($query, $connection))		
		return TRUE;
	else
		return FALSE;
	
}

	/**
	*@desc This function adds the report information sent by user into the database.
	*@param int $user_id
	*@param int $park_id
	*@param string $report
	*@param 
	*/
function addReport($user_id, $park_id, $report)
{
	global $connection; 
	
	// Prepare query
	$query = "INSERT INTO reports (report_id, park_id, user_id, report) "
	. "VALUES (NULL, "
	. "'" . $park_id. "', "
	. "'" . $user_id . "', "
	. "'" . $report . "')";

	
	// Add Report
	if ($result = mysql_query($query, $connection))		
		return TRUE;
	else
		return FALSE;
	
}

	/**
	*@desc This function adds the comment information sent by user into the database.
	*@param int $user_id
	*@param int $park_id
	*@param string $comment
	*/
function addComment($user_id, $park_id, $comment)
{
	global $connection; 
	
	// Prepare query
	$query = "INSERT INTO comments (comment_id, user_id, park_id, comment) "
	. "VALUES (NULL, "
	. "'" . $user_id. "', "
	. "'" . $park_id . "', "
	. "'" . $comment . "')";

	
	// Add comment
	if ($result = mysql_query($query, $connection))	
		return TRUE;	
	else
		return FALSE;
	
}
	/**
	*@desc This function adds the rating information sent by user into the database.
	*@param int $user_id
	*@param int $park_id
	*@param int $rating
	*/
function addRating($user_id, $park_id, $rating)
{
	global $connection; // make accessible in function
	
	$query = "INSERT INTO ratings (park_id, user_id, rating) "
	. "VALUES ('" . $park_id. "', "
	. "'" . $user_id . "', "
	. $rating . ")";
	
	
	// Add Rating
	if ($result = mysql_query ($query, $connection))
		return TRUE;
	else
		return FALSE;
	
}
	/**
	*@desc This function checks if the user has rated a park by checking the database.
	*@param int $user_id
	*@param int $park_id
	*/
function hasRated($user_id, $park_id)
{
	global $connection; // make accessible in function
	
	// Prepare query
	$query = "SELECT rating FROM ratings" .
	" WHERE park_id = " . $park_id . 
	" AND user_id = " . $user_id;
	
	
	// Check Rating
	if ($result = mysql_query ($query, $connection)) // If query worked		
		
		if (mysql_num_rows($result) == 1) // If there is a result	
		{
			$row = mysql_fetch_array($result);
			return $row["rating"];
		}
		else
			return FALSE;
			
	else
		return FALSE;
	
	
}
	/**
	*@desc This function gets the comment information sent by user into the database.
	*@param int $user_id
	*@param int $park_id
	*@param int $limit
	*/
	
function getComments($park_id, $start, $limit)
{
	//Business login in parkdetails script.
	
	global $connection; // make accessible in function
	
	//Comments	
	$query = "SELECT comments.comment_id, users.firstname, comments.user_id, comments.comment"
	. " FROM comments JOIN users ON (comments.user_id = users.user_id)"
	. " WHERE park_id = " . $park_id
	. " LIMIT " . ($start) . " , " . ($limit);
	
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ($query . "<br/>Comment Query Failed");
	else
	{
		// If there are comments
		if (mysql_num_rows($result) > 0)
			return $result;
		else
			return FALSE;
			
	}
}


	/**
	*@desc This function deletes the comment information from the database.
	*@param int $comment_id
	*/
function deleteComment($comment_id)
{

	global $connection; // make accessible in function
	
	//Comments	
	$query = "DELETE FROM comments"
	. " WHERE comment_id = " . $comment_id;
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ($query."<br/>Comment Delete Query Failed");
	else	
		return TRUE;

}


	/**
	*@desc This function gets the equipment information from the database.
	*@param int $park_id
	*/
function getEquipment($park_id)
{
	global $connection; // make accessible in function
	
	// Equipment
	$query = "SELECT name, qty "
	. "FROM equipment "
	. "JOIN parkequipment ON (equipment.equip_id = parkequipment.equip_id) "
	. "WHERE park_id = " . $park_id;
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ("Equipment Query Failed");
	else
	{
		// If there is equipment
		if (mysql_num_rows($result) > 0)
			return $result;
		else
			echo FALSE;
	}

}

	/**
	*@desc This function gets the equipment information from the database.
	*@param string $qry
	*@param int $start
	*@param int $limit
	*/
function getParks($qry, $start, $limit)
{
	//Business logic in findscript.php script.
	
	global $connection; // make accessible in function
	
	//Comments	
	$query = $qry	. " LIMIT " . ($start) . " , " . ($limit);
	
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ($query . "<br/>Result Query Failed");
	else
	{
		// If there are comments
		if (mysql_num_rows($result) > 0)
			return $result;
		else
			return FALSE;
			
	}
}

	/**
	*@desc This function gets the top 5 parks based on our rating system through the database, it uses the overall average rating.
	*/
function getTop5()
{
	
	global $connection; // make accessible in function
	
	//Top 5	
	$query = "SELECT parks.park_id, parks.name, avg(ratings.rating)" .
	" FROM parks JOIN ratings ON (parks.park_id = ratings.park_id)" .
	" GROUP BY park_id ORDER BY avg(ratings.rating) DESC LIMIT 0, 5";
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ($query . "<br/>Top 5 Query Failed");
	else
	{
		// If there are ratings
		if (mysql_num_rows($result) > 0)
			return $result;
		else
			return FALSE;
			
	}
}
	/**
	*@desc This function counts and adds the results found.
	*/
function getResultsCount($query)
{
	//Business logic in findscript script.
	
	global $connection; // make accessible in function
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ($query . "<br/>Results Count Query Failed");
	else	
		return mysql_num_rows($result);
}

	/**
	*@desc This function retrieves the park information from the database. Information includes Park ID, name, suburb, name, type
	*@param int $park_id
	*/
function getParkInfo($park_id)
{

	global $connection; // make accessible in function

	$selects = "parks.park_id, suburbs.name AS 'subname', suburbs.postcode, parks.type, parks.name, parks.address";
	$froms = "parks JOIN suburbs ON (parks.suburb_id = suburbs.suburb_id)";
	
	// Build query
	$query = "SELECT " . $selects . " FROM " . $froms . " WHERE parks.park_id = " . $park_id;
	
	// Run query
	if (!$result = mysql_query ($query, $connection))
		die ("Park Detail Query Failed");		
	else
	{
		// If found a park with that ID
		If (mysql_num_rows($result) > 0)
			return $result;
		else
			return FALSE;		
	}

}
